insert overwrite table jms_dm.dm_sqs_tab_take_arr_network_dt partition (dt)
select network_code     as network_code
     , network_name     as network_name
     , sum(taking_cnt)  as taking_cnt
     , sum(arrival_cnt) as arrival_cnt
     , dt               as date_time
     , dt
from (
         select pick_network_code as network_code
              , net.name          as network_name
              , count(1)          as taking_cnt
              , 0                 as arrival_cnt
              , dt
         from (
                  select waybill_no,
                         pick_network_code,
                         dt,
                         if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                  from (
                           select waybill_no
                                , pick_network_code
                                , dt
                                , substr(input_time, 1, 10) as input_day
                           from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                           where dt >= date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and dt <= '{{ execution_date | cst_ds }}'
                             and substr(input_time, 1, 10) >=
                                 date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                       ) a
                           left join
                       (
                           select del_network_code, create_day
                           from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                           where dt >= date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and dt <= '{{ execution_date | cst_ds }}'
                             and del_network_code is not null
                             and create_day is not null
                           group by del_network_code, create_day
                       ) b
                       on nvl(a.pick_network_code, 'test') = b.del_network_code
                           and nvl(a.input_day, '9999-12-01') = b.create_day
              ) tt
                  left join
              jms_dim.dim_network_whole_massage net
              on pick_network_code = net.code
         where net.network_type = 6
           and fenmu_tichu_flag <> 1
         group by pick_network_code,net.name, dt
--

         union all

         select scan_site_code    as netwrok_code
              , net.name          as network_name
              , 0                 as taking_cnt
              , count(waybill_no) as arrival_cnt
              , dt
         from (
                  select scan_site_code,
                         waybill_no,
                         dt,
                         if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                  from (
                           select scan_site_code
                                , waybill_no
                                , date(scan_time) as                                                                     scan_day
                                , date(scan_time) as                                                                     dt
                                , row_number()
                                   over (partition by waybill_no,scan_site_code,date(scan_time) order by scan_time desc) rnk
                           from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                           where dt >= date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and dt <= '{{ execution_date | cst_ds }}'
                             and substr(scan_time, 1, 10) >=
                                 date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                             and waybill_no like 'JT%'
                             and length(waybill_no) <= 15
                       ) a
                           left join
                       (
                           select del_network_code, create_day
                           from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                           where dt >= date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
                             and dt <= '{{ execution_date | cst_ds }}'
                             and del_network_code is not null
                             and create_day is not null
                           group by del_network_code, create_day
                       ) b
                       on nvl(a.scan_day, '9999-12-01') = b.create_day
                           and nvl(a.scan_site_code, 'test') = b.del_network_code
                  where a.rnk = 1
              ) arrival
                  left join jms_dim.dim_network_whole_massage net
                            on scan_site_code = net.code
         where net.network_type = 6
           and fenmu_tichu_flag <> 1
         group by arrival.scan_site_code, net.name,dt
     ) merges
group by network_code
       , network_name
       , dt
distribute by dt